<?php


// 其他响应内容或处理逻辑
namespace app\controller;

use think\facade\Request;
use app\BaseController;
use think\facade\Db;
use think\response\Json;

class Mysql extends BaseController
{
    public function sayHello()
    {
        return 'hello';
    }

    //获取所有
    public function mysql_get_all(string $input_table_name)
    {
        $user = Db::name($input_table_name)->select();
        return json($user);
    }

    //通过入库操作id有无均考虑
    public function mysql_student_add_byid(string $input_id, string $input_name, string $input_affiliation, string $input_major, string $input_max_borrow)
    {
        $input_table_name = "总学生信息";
        $test1 = Db::name($input_table_name)->where("sid", $input_id)->findOrEmpty();
        if ($test1) {
            return 'error:not empty';
        } else {
            $update_data = ['sid' => $input_id, 'sname' => $input_name, 'saffiliation' => $input_affiliation, 'smajor' => $input_major, 'smax_borrow' => $input_max_borrow];
            Db::name($input_table_name)->insert($update_data);
        }
    }



    //根据id查找值
    public function mysql_get_bygid(string $input_table_name)
    {
        $input_bookid = Request::param('input_bookid');
        $user = Db::name($input_table_name)->where("bid", $input_bookid)->findOrEmpty();
        return json($user);
    }

    //根据name查找值
    public function mysql_get_bygname(string $input_table_name)
    {
        $input_bookname = Request::param('input_bookname');
        $user = Db::name($input_table_name)->where("bname", $input_bookname)->findOrEmpty();
        return json($user);
    }

    //借书操作
    public function mysql_borrowbook_bybookid()
    {
        $input_stuid = Request::param('input_stuid');
        $input_bookid = Request::param('input_bookid');
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";
        $old = Db::name($book_table_name)->where("bid", $input_bookid)->find();
        if ($old) {
            $old_number = Db::name($book_table_name)->where("bid", $input_bookid)->value("bnumber");
            $stu = Db::name($stu_table_name)->where("sid", $input_stuid)->find();
            if (!$stu) {
                //学生不存在
                return 'error3';
            }
            if ($old_number <= 0) {
                //书籍为借完了
                return 'error2';
            }

            $judge = $this->ifDateOut($input_stuid);
            if ($judge != 'no problem') {
                return ('date error ' . $judge);
            }
            $bnumber = $old_number - 1;
            //更新书籍数据
            $update_data0 = ['bnumber' => $bnumber];
            Db::name($book_table_name)->where("bid", $input_bookid)->save($update_data0);
            //更新借书记录，和用户目前可以借的书籍数量
            $currentDate = getdate();

            $test = Db::name($borrow_table_name)->where("bid", $input_bookid)->where("sid", $input_stuid)->find();
            if ($test) {
                Db::table($borrow_table_name)
                    ->where('sid', $input_stuid)
                    ->where('bid', $input_bookid)
                    ->inc('number', '1')
                    ->update();
            } else {
                $borrow_data = ['sid' => $input_stuid, 'bid' => $input_bookid, 'date' => $currentDate["yday"], 'number' => "1"];
                Db::name($borrow_table_name)->insert($borrow_data);
            }

            Db::table($stu_table_name)
                ->where('sid', $input_stuid)
                ->dec('smax_borrow', '1')
                ->update();
            return 'success';
        } else {
            //查无此书
            return 'error1';
        }
    }
    public function mysql_borrowbook_bybookname()
    {
        $input_stuid = Request::param('input_stuid');
        $input_bookname = Request::param('input_bookname');
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";
        $old = Db::name($book_table_name)->where("bname", $input_bookname)->find();
        $input_bookid = Db::name($book_table_name)->where("bname", $input_bookname)->value('bid');
        if ($old) {
            $old_number = Db::name($book_table_name)->where("bid", $input_bookid)->value("bnumber");
            $stu = Db::name($stu_table_name)->where("sid", $input_stuid)->find();
            if (!$stu) {
                //学生不存在
                return 'error3';
            }
            if ($old_number <= 0) {
                //书籍为借完了
                return 'error2';
            }

            $judge = $this->ifDateOut($input_stuid);
            if ($judge != 'no problem') {
                return ('date error ' . $judge);
            }
            $bnumber = $old_number - 1;
            //更新书籍数据
            $update_data0 = ['bnumber' => $bnumber];
            Db::name($book_table_name)->where("bid", $input_bookid)->save($update_data0);
            //更新借书记录，和用户目前可以借的书籍数量
            $currentDate = getdate();

            $test = Db::name($borrow_table_name)->where("bid", $input_bookid)->where("sid", $input_stuid)->find();
            if ($test) {
                Db::table($borrow_table_name)
                    ->where('sid', $input_stuid)
                    ->where('bid', $input_bookid)
                    ->inc('number', '1')
                    ->update();
            } else {
                $borrow_data = ['sid' => $input_stuid, 'bid' => $input_bookid, 'date' => $currentDate["yday"], 'number' => "1"];
                Db::name($borrow_table_name)->insert($borrow_data);
            }

            Db::table($stu_table_name)
                ->where('sid', $input_stuid)
                ->dec('smax_borrow', '1')
                ->update();
            return 'success';
        } else {
            //查无此书
            return 'error1';
        }
    }

    //添加/更改书籍、
    public function mysql_add_book()
    {
        $book_table_name = "可借阅图书";
        $input_bookid = Request::param('input_bookid');
        $input_bookname = Request::param('input_bookname');
        $input_bookdate = Request::param('input_bookdate');
        $input_boookpublisher = Request::param('input_boookpublisher');
        $input_bookposition = Request::param('input_bookposition');
        $input_booknumber = Request::param('input_booknumber');
        $test = Db::name($book_table_name)->where("bid", $input_bookid)->find();
        if ($test) {
            $updata = ["bname" => $input_bookname, "bdate" => $input_bookdate, "bpublisher" => $input_boookpublisher, "bposition" => $input_bookposition, "bnumber" => $input_booknumber];
            Db::name($book_table_name)->where("bid", $input_bookid)->update($updata);
        } else {
            $updata = ["bid" => $input_bookid, "bname" => $input_bookname, "bdate" => $input_bookdate, "bpublisher" => $input_boookpublisher, "bposition" => $input_bookposition, "bnumber" => $input_booknumber];
            Db::name($book_table_name)->insert($updata);
        }
        return 'success';
    }
    public function query_delete_book()
    {
        $book_table_name = "可借阅图书";
        $input_bookid = Request::param('input_bookid');
        $test = Db::name($book_table_name)->where("bid", $input_bookid)->find();
        if ($test) {
            Db::name($book_table_name)->where('bid', $input_bookid)->delete(true);
        } else {
            //没有该书籍
            return 'error null';
        }
        return 'success';
    }

    //判断是否有书逾期
    public function ifDateOut($stuid)
    {
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";

        $test = Db::name($borrow_table_name)->where("sid", $stuid)->find();
        if ($test) {
            $all_date = Db::name($borrow_table_name)->where("sid", $stuid)->column('date');
            $currentDate = getdate();
            //跨越年的问题暂未解决
            for ($i = 0; $i < count($all_date); $i++) {
                if ((int) $currentDate["yday"] - (int) $all_date[$i] > 30) {
                    return ((int) $currentDate["yday"] - (int) $all_date[$i] - 30);
                }
            }
            return 'no problem';
        } else {
            return 'no problem';
        }
    }


    //判断是否这本书逾期返回逾期时间
    public function ifBookDateOut($stuid, $bookid)
    {
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";

        $test = Db::name($borrow_table_name)->where("sid", $stuid)->where("bid", $bookid)->find();
        if ($test) {
            $date = Db::name($borrow_table_name)->where("sid", $stuid)->where("bid", $bookid)->value('date');
            $currentDate = getdate();
            //跨越年的问题暂未解决

            if ((int) $currentDate["yday"] - (int) $date > 30) {
                return ((int) $currentDate["yday"] - (int) $date - 30);
            } else {
                return 'no problem';
            }
        } else {
            return '没有查阅到借阅信息';
        }
    }
    //还书操作
    public function returnBookById()
    {
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";
        $input_stuid = Request::param('input_stuid');
        $input_bookid = Request::param('input_bookid');
        $judge = $this->ifBookDateOut($input_stuid, $input_bookid);
        if ($judge == '没有查阅到借阅信息') {
            return ('没有查阅到借阅信息');
        } else if ($judge == 'no problem') {
            $old_number = Db::name($borrow_table_name)->where("sid", $input_stuid)->where("bid", $input_bookid)->value("number");
            if ($old_number) {
                Db::table($book_table_name)
                    ->where('bid', $input_bookid)
                    ->inc('bnumber', '1')
                    ->update();
                Db::table($stu_table_name)
                    ->where("sid", $input_stuid)
                    ->inc('smax_borrow', '1')
                    ->update();
                if ($old_number == 1) {
                    Db::name($borrow_table_name)->where('sid', $input_stuid)->where('bid', $input_bookid)->delete(true);
                } else {
                    Db::table($borrow_table_name)
                        ->where("sid", $input_stuid)
                        ->where("bid", $input_bookid)
                        ->dec('number', '1')
                        ->update();
                }
                return ('success');
            } else {
                return ('没有该借阅记录');
            }
        } else {
            return ('date error ' . $judge);
        }
    }

    //查询个人借书记录
    public function queryBorrow($input_stuid)
    {
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";
        $all = Db::name($borrow_table_name)->where("sid", $input_stuid)->select()->tojson();
        // 将JSON字符串转换为二维关联数组
        $all_array = json_decode($all, true);

        $returnData = array();
        for ($i = 0; $i < count($all_array); $i++) {
            $student_name = Db::name($stu_table_name)->where("sid", $all_array[$i]['sid'])->value('sname');
            $book_id = $all_array[$i]['bid'];
            $book_name = Db::name($book_table_name)->where("bid", $all_array[$i]['bid'])->value('bname');
            $book_date = Db::name($borrow_table_name)->where("bid", $all_array[$i]['bid'])->value('date');
            $book_number = Db::name($borrow_table_name)->where("bid", $all_array[$i]['bid'])->value('number');
            array_push($returnData, [$student_name, $book_id, $book_name, $book_date, $book_number]);
        }
        $finaldata = json($returnData);
        return $finaldata;
    }

    //缴费(如果借阅同一书多本处理会有误)
    function payForBook()
    {
        $book_table_name = "可借阅图书";
        $stu_table_name = "总学生信息";
        $borrow_table_name = "借阅信息";
        $input_stuid = Request::param('input_stuid');
        $input_bookid = Request::param('input_bookid');
        $judge = $this->ifBookDateOut($input_stuid, $input_bookid);
        if ($judge == '没有查阅到借阅信息') {
            return ('没有查阅到借阅信息');
        } else {
            $currentDate = getdate();
            $num = Db::table($borrow_table_name)->where('sid', $input_stuid)->where('bid', $input_bookid)->value('number');
            $old_date = Db::table($borrow_table_name)->where('sid', $input_stuid)->where('bid', $input_bookid)->value('date');
            $money = $num * ($currentDate["yday"] - $old_date - 30) * 0.02;
            Db::name($borrow_table_name)
                ->where('sid', $input_stuid)
                ->where('bid', $input_bookid)
                ->update(['date' => $currentDate["yday"]]);
            return ('已缴纳' . $money . "元!");
        }
    }
}
